﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Data.SQLite;

namespace LuZhouLaoJiao
{
    public partial class InquiryResult : Form
    {
        public InquiryResult()
        {
            InitializeComponent();
        }

        public string doublePiCi = null;
        public int doubleRow;
        public float avgAcidity = 0;
        public DataTable dt = new DataTable();
        //MySqlConnection conn = null;
        //MySqlCommand comm = null;
        //MySqlDataReader reader = null;

        SQLiteConnection conn = null;
        SQLiteCommand comm = null;
        SQLiteDataReader reader = null;
//         string strCon = null;
//         string dbUser = Global.getRegeditValue("localDBUser");
//         string dbPwd = Global.getRegeditValue("localDBPwd");
//         string database = Global.getRegeditValue("localDatabase");
//         string ip = Global.getRegeditValue("localIP");

        private void InquiryResult_Load(object sender, EventArgs e)
        {
            string sqlPici = "";
            string sqlSuandu = "";
            string sqlJianceren = "";
            string sqlLaiyuan = "";
            string sqlRiqi = "";
            string sqlRemark = "";

            InquiryInput input = (InquiryInput)this.Owner;
            string sqlComm = "select * from batch_info where ";

            //获得查询子句
            if (input.tbPici.Text.ToString() != "")
            {
               sqlPici = "pici_id like '%" + input.tbPici.Text.ToString() + "%'";
            }
            if (input.tbSuandu.Text.ToString() != "")
            {
                if (input.tbSuandu.Text.ToString().Contains(',') || input.tbSuandu.Text.ToString().Contains('，'))
                {
                    char[] split = new char[] { ',', '，' };
                    string[] values = input.tbSuandu.Text.ToString().Split(split);
                    //MessageBox.Show(values[0] + " " + values[1]);
                    
                    if (values[0] != "" && values[1] != "")
                    {
                        if (!Global.isNumber(values[1]) || !Global.isNumber(values[0]))
                        {
                            MessageBox.Show("酸度范围的值必须为数字.");
                            this.Close();
                            //return;
                        }
                        sqlSuandu = "suandu > " + values[0] + " and suandu < " + values[1];
                    }
                    if (values[0] == "" && values[1] != "")
                    {
                        if (!Global.isNumber(values[1]))
                        {
                            MessageBox.Show("酸度范围的值必须为数字.");
                            this.Close();
                            //return;
                        }
                        sqlSuandu = "suandu < " + values[1];
                    }
                    if (values[0] != "" && values[1] == "")
                    {
                        if (!Global.isNumber(values[0]))
                        {
                            MessageBox.Show("酸度范围的值必须为数字.");
                            this.Close();
                            //return;
                        }
                        sqlSuandu = "suandu > " + values[0];
                    }
                }
                else
                {
                    if (!Global.isNumber(input.tbSuandu.Text.ToString()))
                    {
                        MessageBox.Show("酸度值必须为数字.");
                        this.Close();
                        //return;
                    }
                    sqlSuandu = "suandu = " + input.tbSuandu.Text.ToString();
                }
                
            }
            if (input.tbJianceren.Text.ToString() != "")
            {
                sqlJianceren = "jianceren like '%" + input.tbJianceren.Text.ToString() + "%'";
            }
            if (input.tbLaiyuan.Text.ToString() != "")
            {
                sqlLaiyuan = "laiyuan like '%" + input.tbLaiyuan.Text.ToString() + "%'";
            }
            if (input.tbRiqi.Text.ToString() != "")
            {
                string riqi = input.tbRiqi.Text.ToString();
                if (riqi.Contains("."))
                {
                    riqi = riqi.Replace('.', '-');
                }
                if (riqi.Contains(',') || riqi.Contains('，'))
                {
                    char[] split = new char[] { ',', '，' };
                    string[] values = riqi.Split(split);
                    if (values[0] != "" && values[1] != "")
                    {
                        sqlRiqi = "riqi > '" + values[0] + "' and riqi < '" + values[1] + "'";
                    }
                    if (values[0] == "" && values[1] != "")
                    {
                        sqlRiqi = "riqi < '" + values[1] + "'";
                    }
                    if (values[0] != "" && values[1] == "")
                    {
                        sqlRiqi = "riqi > '" + values[0] + "'";
                    }
                }
                else
                {
                    sqlRiqi = "riqi = '" + riqi + "'";
                }
            }
            if (input.tbRemark.Text.ToString() != "")
            {
                sqlRemark = "beizhu like '%" + input.tbRemark.Text.ToString() + "%'";
            }

            //组合成查询语句
            if (sqlPici != "")
            {
                sqlComm += sqlPici + " and ";
            }
            if (sqlSuandu != "")
            {
                sqlComm += sqlSuandu + " and ";
            }
            if (sqlJianceren != "")
            {
                sqlComm += sqlJianceren + " and ";
            }
            if (sqlLaiyuan != "")
            {
                sqlComm += sqlLaiyuan + " and ";
            }
            if (sqlRiqi != "")
            {
                sqlComm += sqlRiqi + " and ";
            }
            if (sqlRemark != "")
            {
                sqlComm += sqlRemark + " and ";
            }
            sqlComm = sqlComm.Substring(0, sqlComm.Length - 4);
            sqlComm += " order by pici_id desc;";
            //MessageBox.Show(sqlComm);
            //MessageBox.Show(sqlComm);

            //连接数据库
            //strCon = "server = " + ip + "; user id = " + dbUser + "; password = " + dbPwd + "; database = " + database + "; persist security info = false";
            //conn = new MySqlConnection(strCon);
            //comm = new MySqlCommand();


            conn = new SQLiteConnection("Data Source=./data.db");
            comm = new SQLiteCommand();
            //MessageBox.Show("sqlite");
            conn.Open();
            comm = conn.CreateCommand();
            comm.Connection = conn;
            comm.CommandText = sqlComm;
            try
            {
                reader = comm.ExecuteReader();
                if (!reader.HasRows)
                {
                    MessageBox.Show("没有符合条件的查询结果.");
                    this.Close();
                    return;
                }
                

                dt.Load(reader);
                dt.Columns[0].ColumnName = "批次号";
                dt.Columns[1].ColumnName = "平均酸度";
                dt.Columns[2].ColumnName = "检测人";
                dt.Columns[3].ColumnName = "样本来源";
                dt.Columns[4].ColumnName = "检测日期";
                dt.Columns[5].ColumnName = "检测时间";
                dt.Columns[6].ColumnName = "备注";

                dataGridView1.DataSource = dt;
                dataGridView1.Columns[0].ReadOnly = true;
                dataGridView1.Columns[0].Width = 120;
                dataGridView1.Columns[1].ReadOnly = true;
                dataGridView1.Columns[2].ReadOnly = true;
                dataGridView1.Columns[4].ReadOnly = true;
                dataGridView1.Columns[5].ReadOnly = true;

                this.Width = 780;

            }
            catch (System.Exception ex)
            {
                //MessageBox.Show(ex.GetType().ToString());
                MessageBox.Show("数据库读取错误,请检查输入的查询条件是否合适。");
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }
        }

        private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            Global.inquiried = true;
            //MessageBox.Show(dataGridView1[e.ColumnIndex, e.RowIndex].Value.ToString());
            if (e.ColumnIndex == 0)
            {
                if (dataGridView1[1, e.RowIndex].Value.ToString().CompareTo("") == 0)
                {
                    //MessageBox.Show("无值");
                    doublePiCi = dataGridView1[0, e.RowIndex].Value.ToString();
                    doubleRow = e.RowIndex;
                    TestAcidity testAcidity = new TestAcidity();
                    testAcidity.ShowDialog(this);
                }
                else
                {
                    //MessageBox.Show("有值");
                    doublePiCi = dataGridView1[0, e.RowIndex].Value.ToString();
                    BatchDetailsInfo inquiryItem = new BatchDetailsInfo();
                    inquiryItem.ShowDialog(this);
                }
            }

        }

        private void dataGridView1_CellParsing(object sender, DataGridViewCellParsingEventArgs e)
        {
            string header = dataGridView1.Columns[e.ColumnIndex].HeaderText;
            comm.CommandText = "update batch_info set " + Global.headerMatch(header) + " = '" + e.Value.ToString() + "' where pici_id = '" + dataGridView1[0, e.RowIndex].Value + "';";
            try
            {
                comm.ExecuteNonQuery();
                //MessageBox.Show("执行更新");
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("数据库更新值错误：" + ex.Message);
            }
        }

        private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            if (dataGridView1[1, e.RowIndex].Value.ToString() == "")
            {
                MessageBox.Show("酸度值不能修改为空");
                return;
            }
            comm.CommandText = "update batch_info set suandu = '" + avgAcidity + "' where pici_id = '" + doublePiCi + "';";
            try
            {
                comm.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("无法写入平均酸度：" + ex.Message);
            }
        }

        private void InquiryResult_FormClosed(Object sender, FormClosedEventArgs e)
        {
            //MessageBox.Show("将要关闭窗口");
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
            

            InquiryInput input = (InquiryInput)this.Owner;

            if (!input.cbNotClose.Checked)
            {
                input.Close();
                //MessageBox.Show("关闭");
            }

            
        }
    }
}
